package com.tanhua.admin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.model.db.Log;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface LogMapper extends BaseMapper<Log> {

    /**
     * 1、根据时间和类型统计总数
     */
    @Select("select count(DISTINCT user_id) from tb_log where log_time =#{logTime} and type=#{type} ")
    Integer queryByLogTimeAndType(@Param("logTime") String logTime, @Param("type") String type);

    /**
     * 2、根据时间统计总数
     */
    @Select("select count(DISTINCT user_id) from tb_log where log_time =#{logTime}")
    Integer queryByLogTime(String logTime);

    /**
     * 3、查询次日留存数
     */
    @Select("select count(DISTINCT user_id) from tb_log where log_time =#{yesterday} and user_id in \n" +
            "( \n" +
            "\t select user_id from tb_log where log_time =#{today} and type='0102' \n" +
            ")")
    Integer queryNumRetention1d(@Param("today") String today, @Param("yesterday") String yesterday);

    /**
     * 4.累计用户查询 注册的总人数
     */
    @Select("select count(DISTINCT user_id) from tb_log where type = '0102'")
    Integer queryCount();

    ////根据月份年份查询留存
    @Select("select COUNT(DISTINCT user_id) from tb_log where  MONTH(log_time) = #{month} and YEAR(log_time) = #{year} and user_id in\n" +
            "(\n" +
            "SELECT user_id from tb_log where MONTH(log_time) = #{lastMonth} and YEAR(log_time) = #{year} and type = '0102' \n" +
            ")\n")
    Integer queryRetent(@Param("month") Integer month,@Param("year") Integer year ,@Param("lastMonth") Integer lastMonth);
}
